# 已在navicat中运行正常
# Create(增)：
# 单条插入 INSERT INTO class2(unit_price, quantity) VALUES(3.4, 2)
# 多条插入 INSERT INTO class2(unit_price, quantity) VALUES(3.4, 2),(5, 5),(6, 5)

# Retrieve(查)：
# 获取所有记录：SELECT product_name FROM class2
# 条件查询:  SELECT * FROM class2 where  product_name="香蕉" and id =1
# 模糊匹配(`LIKE`, `%`) :  SELECT * FROM class2 where  product_name like "%香%"
# 限制返回条数(`LIMIT`):  SELECT * FROM class2 where  quantity=5  LIMIT 2
# 过滤重复值(`DISTINCT`) ： SELECT distinct  unit_price，quantity FROM class2  where  quantity=5
# 升序(`ASC`) : SELECT quantity FROM class2 ORDER BY quantity ASC
# 降序(`DESC`) : SELECT quantity FROM class2 ORDER BY quantity DESC
# 获取查询结果的条数: SELECT COUNT(*) FROM  class2 ORDER BY quantity ASC

# Update(更新)
# UPDATE class2  SET quantity = 8 WHERE id=11

# Delete(删除)
# DELETE FROM class2  WHERE quantity = 8

# 并集`UNION`
# SELECT unit_price from class1 WHERE unit_price is not NULL
# UNION
# SELECT unit_price from class2 WHERE unit_price is not NULL

# 交集(`JOIN`, `INNER JOIN`)
# SELECT s1.unit_price FROM
# (SELECT unit_price from class1 WHERE unit_price is not NULL) as s1
# JOIN
# (SELECT unit_price from class2 WHERE unit_price is not NULL) as s2
# ON s1.unit_price=s2.unit_price

# 差集(`LEFT JOIN`, `RIGHT JOIN`):
# SELECT * FROM
# (SELECT unit_price from class1 WHERE unit_price is not NULL) as s1
# LEFT JOIN
# (SELECT unit_price from class2 WHERE unit_price is not NULL) as s2
# ON s1.unit_price=s2.unit_price
# WHERE s2.unit_price is NULL

# SELECT * FROM
# (SELECT unit_price from class1 WHERE unit_price is not NULL) as s1
# RIGHT JOIN
# (SELECT unit_price from class2 WHERE unit_price is not NULL) as s2
# ON s1.unit_price=s2.unit_price
# WHERE s1.unit_price is NULL

# 补集
# SELECT * FROM
# (SELECT unit_price from class1 WHERE unit_price is not NULL) as s1
# LEFT JOIN
# (SELECT unit_price from class2 WHERE unit_price is not NULL) as s2
# ON s1.unit_price=s2.unit_price
# WHERE s2.unit_price is NULL
# UNION
# SELECT * FROM
# (SELECT unit_price from class1 WHERE unit_price is not NULL) as s1
# RIGHT JOIN
# (SELECT unit_price from class2 WHERE unit_price is not NULL) as s2
# ON s1.unit_price=s2.unit_price
# WHERE s1.unit_price is NULL
